--------------------------------------------------------------------------------
-- @name: index split
-- @author: dion cho
-- @note: test case for the impact of the index key order
--------------------------------------------------------------------------------

set pages 10000
set lines 200
set timing off
set trimspool on

col item format a30
col deltas format a50

@capture_on

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
drop table t4 purge;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);
create table t4(c1 number, c2 number) partition by hash(c1) partitions 4;

create index t1_right_idx on t1(c1);
create index t2_random_idx on t2(c1);
create index t3_reverse_idx on t3(c1) reverse;
create index t4_hashp_idx on t4(c1) local;


exec tpack.begin_session_snapshot;

-- right handed insertion
insert into t1 select level, 1 from dual connect by level <= 200000;
commit;

exec tpack.add_session_snapshot;

-- random insertion
insert into t2 select level, 1 from dual connect by level <= 200000 order by dbms_random.random;
commit;

exec tpack.add_session_snapshot;

-- reverse insertion
insert into t3 select level, 1 from dual connect by level <= 200000;
commit;

exec tpack.add_session_snapshot;

-- hashp insertion
insert into t4 select level, 1 from dual connect by level <= 200000;
commit;

exec tpack.add_session_snapshot;

-- diff report
select * from table(tpack.session_snapshot_report) where item like '%splits%' or item like '%redo size%' or item = 'DB time';

-- index size
exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');
exec dbms_stats.gather_table_stats(user, 't3');
exec dbms_stats.gather_table_stats(user, 't4');

select index_name, leaf_blocks
from dba_ind_statistics 
where owner = user and table_name in ('T1', 'T2', 'T3', 'T4') and partition_name is null;


-- range scan performance
select /*+ gather_plan_statistics index(t1) */ count(*)
from t1
where c1 between 1 and 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


select /*+ gather_plan_statistics index(t2) */ count(*)
from t2
where c1 between 1 and 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t3) */ count(*)
from t3
where c1 between 1 and 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t4) */ count(*)
from t4
where c1 between 1 and 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));



-- clustering factor
select index_name, clustering_factor
from dba_ind_statistics 
where owner = user and table_name in ('T1', 'T2', 'T3', 'T4') and partition_name is null;


select /*+ gather_plan_statistics index(t1) */ count(c2)
from t1
where c1 between 1 and 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


select /*+ gather_plan_statistics index(t2) */ count(c2)
from t2
where c1 between 1 and 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t3) */ count(c2)
from t3
where c1 between 1 and 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select /*+ gather_plan_statistics index(t4) */ count(c2)
from t4
where c1 between 1 and 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));



@capture_off
